﻿using System;
using System.Configuration;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class FieldDutyDtlsDAL : IFieldDutyDtlsDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public FieldDutyDtlsDAL()
        {
           
        }
        public DataSet LoadFieldDutyDetails(FieldDutyDtlsInfo dInfo)
        {
            string strSQL;
            int FieldDutySeq = dInfo.FieldDuty.Sequence;

            //if (dInfo.FieldDuty != null)
            //    FieldDutySeq = dInfo.FieldDuty.Sequence;

            DataSet ds = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", dInfo.Sequence);
                dbManager.AddParameters(1, "@FieldDutySeq", FieldDutySeq);
                dbManager.AddParameters(2, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT dd.FIELD_DUTY_DTLS_SEQ, dd.FIELD_DUTY_SEQ, dd.DUTY_SEQ"
                            + ", pd.FIELD_POS_SEQ, pd.CLASS_SEQ, dd.SEQ_NO, dd.DESCR, d.DUTY_NAME"
                            + ", f.FIELD_POS_NAME, c.CLASS_NAME, f.POS_TYPE_SEQ, pt.POS_TYPE_NAME"
                        + " FROM HRM_MP_FIELD_DUTY_DTLS dd"
                            + " LEFT JOIN HRM_MP_DUTIES d ON dd.DUTY_SEQ = d.DUTY_SEQ"
                            + " LEFT JOIN HRM_MP_FIELD_DUTIES pd ON dd.FIELD_DUTY_SEQ = pd.FIELD_DUTY_SEQ"
                            + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON pd.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
                            + " LEFT JOIN HRM_MP_CLASSES c ON pd.CLASS_SEQ = c.CLASS_SEQ"
                            + " LEFT JOIN HRM_MP_POSITION_TYPES pt ON f.POS_TYPE_SEQ = pt.POS_TYPE_SEQ"
                    + " WHERE dd.RECORD_STATUS not like @DeleteRecord"
                        + (dInfo.Sequence > 0 ? " AND dd.FIELD_DUTY_DTLS_SEQ = @Sequence" : "")
                        + (FieldDutySeq > 0 ? " AND dd.FIELD_DUTY_SEQ = @FieldDutySeq" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

            return ds;
        }
        public IList<FieldDutyDtlsInfo> ListFieldDutyDetails(FieldDutyDtlsInfo dInfo)
        {
            string strSQL;
            int FieldDutySeq = 0;

            if (dInfo.FieldDuty != null)
                FieldDutySeq = dInfo.FieldDuty.Sequence;

            List<FieldDutyDtlsInfo> infoList = new List<FieldDutyDtlsInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", dInfo.Sequence);
                dbManager.AddParameters(1, "@FieldDutySeq", FieldDutySeq);
                dbManager.AddParameters(2, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT dd.FIELD_DUTY_DTLS_SEQ, dd.FIELD_DUTY_SEQ, dd.DUTY_SEQ"
                        + ", dd.SEQ_NO, dd.DESCR, dd.RECORD_STATUS, dd.CREATED_BY, dd.UPDATED_BY"
                    + " FROM HRM_MP_FIELD_DUTY_DTLS dd"
                    + " WHERE dd.RECORD_STATUS not like @DeleteRecord"
                        + (dInfo.Sequence > 0 ? " AND dd.FIELD_DUTY_DTLS_SEQ = @Sequence" : "")
                        + " AND dd.FIELD_DUTY_SEQ = @FieldDutySeq";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    FieldDutyDAL fdDAL = new FieldDutyDAL();
                    FieldDutyInfo fdInfo = new FieldDutyInfo();
                    if (!rdr.IsDBNull(1))
                    {
                        IList<FieldDutyInfo> fdList = fdDAL.ListFieldDuties(new FieldDutyInfo(rdr.GetInt32(1)));
                        if (fdList.Count > 0)
                            fdInfo = fdList[0];
                    }

                    DutyDAL dDAL = new DutyDAL();
                    DutyInfo dtInfo = new DutyInfo();
                    if (!rdr.IsDBNull(2))
                    {
                        IList<DutyInfo> dList = dDAL.ListDuties(new DutyInfo(rdr.GetInt32(2)));
                        if (dList.Count > 0)
                            dtInfo = dList[0];
                    }

                    FieldDutyDtlsInfo Info = new FieldDutyDtlsInfo(rdr.GetInt32(0)
                        , fdInfo, dtInfo
                        , rdr.IsDBNull(3) ? "" : rdr.GetString(3)
                        , rdr.IsDBNull(4) ? "" : rdr.GetString(4)
                        , rdr.IsDBNull(5) ? "" : rdr.GetString(5)
                        , rdr.IsDBNull(6) ? (int?)null : rdr.GetInt32(6)
                        , rdr.IsDBNull(7) ? (int?)null : rdr.GetInt32(7));
                    infoList.Add(Info);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return infoList;
        }
        //public IList<FieldDutyDtlsInfo> LoadGridFieldDutyDtls(Nullable<int> FieldSeq, Nullable<int> ClassSeq)
        //{
        //    string strSQL;
        //    DataSet ds = null;
        //    DataRowCollection dr = null;
        //    IList<FieldDutyDtlsInfo> infoList = new List<FieldDutyDtlsInfo>();

        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(3);

        //        dbManager.AddParameters(0, "@FieldSeq", FieldSeq);
        //        dbManager.AddParameters(1, "@ClassSeq", ClassSeq);
        //        dbManager.AddParameters(2, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

        //        strSQL = "SELECT dd.FIELD_DUTY_DTLS_SEQ, dd.FIELD_DUTY_SEQ, dd.DUTY_SEQ"
        //                + ", pd.FIELD_POS_SEQ, pd.CLASS_SEQ, dd.SEQ_NO, dd.DESCR, d.DUTY_NAME"
        //                + ", f.FIELD_POS_NAME, c.CLASS_NAME, f.POS_TYPE_SEQ, pt.POS_TYPE_NAME"
        //            + " FROM HRM_MP_FIELD_DUTY_DTLS dd"
        //                + " LEFT JOIN HRM_MP_DUTIES d ON dd.DUTY_SEQ = d.DUTY_SEQ"
        //                + " LEFT JOIN HRM_MP_FIELD_DUTIES pd ON dd.FIELD_DUTY_SEQ = pd.FIELD_DUTY_SEQ"
        //                + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON pd.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
        //                + " LEFT JOIN HRM_MP_CLASSES c ON pd.CLASS_SEQ = c.CLASS_SEQ"
        //                + " LEFT JOIN HRM_MP_POSITION_TYPES pt ON f.POS_TYPE_SEQ = pt.POS_TYPE_SEQ"
        //            + " WHERE dd.RECORD_STATUS not like @DeleteRecord"
        //                + " AND case when @FieldSeq is null then 0 = 0 else pd.FIELD_POS_SEQ = @FieldSeq end"
        //                + " AND case when @ClassSeq is null then 0 = 0 else pd.CLASS_SEQ = @ClassSeq end";

        //        ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
        //        dr = ds.Tables[0].Rows;
        //        if (dr.Count > 0)
        //        {
        //            for (int i = 0; i < dr.Count; i++)
        //            {
        //                FieldDutyDtlsInfo info = new FieldDutyDtlsInfo();
        //                info.Seq = Convert.IsDBNull(dr[i]["FIELD_DUTY_DTLS_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["FIELD_DUTY_DTLS_SEQ"].ToString());
        //                info.FieldDutySeq = Convert.IsDBNull(dr[i]["FIELD_DUTY_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["FIELD_DUTY_SEQ"].ToString());
        //                info.DutySeq = Convert.IsDBNull(dr[i]["DUTY_SEQ"]) ? (int?)null : Int32.Parse((dr[i]["DUTY_SEQ"]).ToString());
        //                info.FieldPosSeq = Convert.IsDBNull(dr[i]["FIELD_POS_SEQ"]) ? (int?)null : Int32.Parse((dr[i]["FIELD_POS_SEQ"]).ToString());
        //                info.ClassSeq = Convert.IsDBNull(dr[i]["CLASS_SEQ"]) ? (int?)null : Int32.Parse((dr[i]["CLASS_SEQ"]).ToString());
        //                info.No = Convert.IsDBNull(dr[i]["SEQ_NO"]) ? "" : (dr[i]["SEQ_NO"]).ToString();
        //                info.Descr = Convert.IsDBNull(dr[i]["DESCR"]) ? "" : (dr[i]["DESCR"]).ToString();
        //                info.DutyName = Convert.IsDBNull(dr[i]["DUTY_NAME"]) ? "" : (dr[i]["DUTY_NAME"]).ToString();
        //                info.FieldPosName = Convert.IsDBNull(dr[i]["FIELD_POS_NAME"]) ? "" : (dr[i]["FIELD_POS_NAME"]).ToString();
        //                info.ClassName = Convert.IsDBNull(dr[i]["CLASS_NAME"]) ? "" : (dr[i]["CLASS_NAME"]).ToString();
        //                infoList.Add(info);
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //    return infoList;
        //}
        
        public void Insert(FieldDutyDtlsInfo dutyDtlsInfo)
        {
            DataSet ds = null;
            DataRowCollection dr = null;
            string strSQL;
            int DutySeq = 0;
            int ClassSeq = 0;
            int FieldSeq = 0;
            string Description = "";

            if (dutyDtlsInfo.FieldDuty != null)
            {
                DutySeq = dutyDtlsInfo.FieldDuty.Sequence;
                Description = dutyDtlsInfo.FieldDuty.Description;
                if (dutyDtlsInfo.FieldDuty.Class != null)
                    ClassSeq = dutyDtlsInfo.FieldDuty.Class.Sequence;
                if (dutyDtlsInfo.FieldDuty.Field != null)
                    FieldSeq = dutyDtlsInfo.FieldDuty.Field.Sequence;
            }           

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@FieldSeq", FieldSeq);
                dbManager.AddParameters(1, "@ClassSeq", ClassSeq);
                dbManager.AddParameters(2, "@DutySeq", dutyDtlsInfo.Duty == null ? (int?)null : dutyDtlsInfo.Duty.Sequence);
                dbManager.AddParameters(3, "@No", dutyDtlsInfo.No);
                dbManager.AddParameters(4, "@Descr", dutyDtlsInfo.Description);
                dbManager.AddParameters(5, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@CreatedBy", dutyDtlsInfo.CreatedBy);
                dbManager.AddParameters(7, "@CreatedWhen", DateTime.Today);

                strSQL = "SELECT FIELD_DUTY_SEQ"
                    + " FROM HRM_MP_FIELD_DUTIES"
                    + " WHERE FIELD_POS_SEQ = @FieldSeq"
                        + " AND CLASS_SEQ = @ClassSeq"
                        + " AND RECORD_STATUS = @RecordStatus";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count <= 0)
                {
                    strSQL = "INSERT INTO HRM_MP_FIELD_DUTIES(FIELD_POS_SEQ, CLASS_SEQ"
                            + ", RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                        + " VALUES(@FieldSeq, @ClassSeq, @RecordStatus, @CreatedBy, @CreatedWhen)";

                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }

                strSQL = "INSERT INTO HRM_MP_FIELD_DUTY_DTLS(FIELD_DUTY_SEQ, DUTY_SEQ"
                        + ", SEQ_NO, DESCR, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                        + " select field_duty_seq, @DutySeq, @No"
                        + ", @Descr, @RecordStatus, @CreatedBy, @CreatedWhen"
                    + " from hrm_mp_field_duties"
                        + " where field_pos_seq = @FieldSeq"
                            + " and class_seq = @ClassSeq"
                            + " and record_status = @RecordStatus";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Update(FieldDutyDtlsInfo dutyDtlsInfo)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@FieldDutyDtlsSeq", dutyDtlsInfo.Sequence);
                dbManager.AddParameters(1, "@DutySeq", dutyDtlsInfo.Duty == null ? (int?)null : dutyDtlsInfo.Duty.Sequence);
                dbManager.AddParameters(2, "@No", dutyDtlsInfo.No);
                dbManager.AddParameters(3, "@Descr", dutyDtlsInfo.Description);
                dbManager.AddParameters(4, "@UpdatedBy", dutyDtlsInfo.UpdatedBy);
                dbManager.AddParameters(5, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_MP_FIELD_DUTY_DTLS"
                           + " SET DUTY_SEQ = @DutySeq"
                                + ", SEQ_NO = @No"
                                + ", DESCR = @Descr"
                                + ", UPDATED_BY = @UpdatedBy"
                                + ", UPDATED_WHEN = @UpdatedWhen"
                           + " WHERE FIELD_DUTY_DTLS_SEQ = @FieldDutyDtlsSeq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Seq", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_MP_FIELD_DUTY_DTLS"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE FIELD_DUTY_DTLS_SEQ = @Seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
